/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package daos;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import models.*;
import util.WebConst;
/**
 *
 * @author trietto
 */
public class OrderDetailDAO {
private PreparedStatement pStmtSelectCount = null;
    private PreparedStatement pStmtSelectAll = null;
    private PreparedStatement pStmtSelect = null;
    private PreparedStatement pStmtInsert = null;
    private PreparedStatement pStmtDelete = null;
    private PreparedStatement pStmtUpdate = null;
    private static OrderDetailDAO orderDetailDAO = null;

    public static OrderDetailDAO singleton() {
        if (orderDetailDAO == null) {
            orderDetailDAO = new OrderDetailDAO();
        }
        return orderDetailDAO;
    }

    public OrderDetailDAO() {
        try {
            pStmtSelectCount = DBConnection.getConnection().prepareStatement("SELECT Count(OrderId) FROM OrderDetails WHERE OrderId = ?");
            pStmtSelectAll = DBConnection.getConnection().prepareStatement("SELECT * FROM OrderDetails WHERE OrderId = ? LIMIT ?,?");
            pStmtSelect = DBConnection.getConnection().prepareStatement("SELECT * FROM OrderDetails WHERE OrderId = ? AND ItemId = ?");
            pStmtInsert = DBConnection.getConnection().prepareStatement("INSERT INTO OrderDetails(OrderId,ItemId,ItemDesc,Price,Quantity) VALUES(?,?,?,?,?)");
            pStmtDelete = DBConnection.getConnection().prepareStatement("DELETE FROM OrderDetails WHERE OrderId = ? AND ItemId = ?");
            pStmtUpdate = DBConnection.getConnection().prepareStatement("UPDATE OrderDetails SET ItemDesc=?,Price=?,Quantity=? WHERE OrderId = ? AND ItemId = ?");
        } catch (SQLException ex) {
            Logger.getLogger(OrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int recordCount(String orderId){
        int totalRecord = 0;
        try {
            pStmtSelectCount.setString(1, orderId);
            ResultSet rs = pStmtSelectCount.executeQuery();
            while (rs.next()) {
                totalRecord = rs.getInt(1);
            }
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(CardTypeDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return totalRecord;
    }

    public ArrayList<OrderDetail> selectAll(String orderId, int page) {
        ArrayList<OrderDetail> lstOrderDetails = new ArrayList<OrderDetail>();
        int offset = (page-1) * WebConst.ROW_PER_PAGE;
        try {
            pStmtSelectAll.setString(1, orderId);
            pStmtSelectAll.setInt(2, offset);
            pStmtSelectAll.setInt(3, WebConst.ROW_PER_PAGE);
            ResultSet rs = pStmtSelectAll.executeQuery();
            while (rs.next()) {
                lstOrderDetails.add(new OrderDetail(rs.getString(1), rs.getInt(2),
                        rs.getString(3), rs.getDouble(4), rs.getInt(5)));
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return lstOrderDetails;
    }

    public OrderDetail select(String orderId, int itemId) {
        OrderDetail orderDetail = null;
        try {
            pStmtSelect.setString(1, orderId);
            pStmtSelect.setInt(2, itemId);
            ResultSet rs = pStmtSelect.executeQuery();
            while (rs.next()) {
                orderDetail = new OrderDetail(rs.getString(1), rs.getInt(2),
                        rs.getString(3), rs.getDouble(4), rs.getInt(5));
            }
        } catch (SQLException ex) {
            Logger.getLogger(OrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return orderDetail;
    }

    public String insert(OrderDetail orderDetail) {
        String result = "";
        boolean bRet = false;
        try {
            pStmtInsert.setString(1, orderDetail.getOrderId());
            pStmtInsert.setInt(2, orderDetail.getItemId());
            pStmtInsert.setString(3, orderDetail.getItemDesc());
            pStmtInsert.setDouble(4, orderDetail.getPrice());
            pStmtInsert.setInt(5, orderDetail.getQuantity());
            pStmtInsert.execute();
            bRet = true;
        } catch (SQLException ex) {
            Logger.getLogger(OrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = bRet ? "Successed" : "Failed";
        return result;
    }

    public String delete(String orderId, int itemId) {
        String result = "";
        int numOfRows = 0;
        try {
            pStmtDelete.setString(1, orderId);
            pStmtDelete.setInt(2, itemId);
            numOfRows = pStmtDelete.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(OrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows>0 ? "Successed" : "Failed";

        return result;
    }

    public String update(OrderDetail orderDetail) {
               String result = "";
        int numOfRows = 0;
        try { 
            pStmtUpdate.setString(1, orderDetail.getItemDesc());
            pStmtUpdate.setDouble(2, orderDetail.getPrice());
            pStmtUpdate.setInt(3, orderDetail.getQuantity());            
            pStmtUpdate.setString(4, orderDetail.getOrderId());
            pStmtUpdate.setInt(5, orderDetail.getItemId());
            numOfRows = pStmtUpdate.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(OrderDetailDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        result = numOfRows>0 ? "Successed" : "Failed";

        return result;
    }
}
